Query Builder

 

Now you are ready to start inputting your desired Report information.

The left side displays the available fields to choose from. (This list will change based on what form the Query Builder icon was activated from). Any Fields in BOLD indicate further choice selections are available in that area. You can click ANY field in bold to expand it. These are "categories" that have several field options which will be listed directly below and slightly indented.

Note: Only one expansion may be visible at any one time. To view a different set of fields simply click another field in bold to collapse the first and expand the next for viewing.

Once you have your field information selection choice displayed in the left pane (NOT IN BOLD, in regular text), it is time to add it to one of the panes on the right.

The right side is where your selected fields and filters will display.

You have two choices here, you can add your selection to the Display field pane or to the Filter field pane.

Use the radio buttons located above the Field Information pane to determine whether you wish to DISPLAY your selection ON the report, or FILTER the report BY your selection.

Note: It is possible to choose the same field information for both panes, you will have to add your selection TWICE, once with the display button selected, then again with the filter button selected.

Make sure to select the Add to Display box button when you are selecting Display Fields, (top right screen) and the Add to Filter box button when you are adding Filter fields (bottom right screen).

You can add as many fields as you wish to either the Display or Filter panes.

Once you have added a field to either the Display or Filter panes, there will be a delete button next to it - if you have added a field by mistake, or decide later to remove it for any reason, simply click the delete button IN THE PANE to do so.

Note: You do NOT have to have any fields entered into the Filter field panel to generate a Report. If your expected data is relatively small, you may not need to filter it.

The Display Field Name section contains the fields that will appear on your report from left to right. Once you click on the selection choice it will be added to the designated pane. It will now be visible in the pane you chose on the right side of the screen.

.

Here, the selected field is added to the Display Field Name section. The column title now has an entry. The delete button will also appear to allow removal of the selected field if desired, and up and down arrows to move the field up or down the list.

The Filter Field Name section contains the fields that will allow you to add or alter parameters for limiting the information that appears on your report.

To add a filter, make sure the Add to Filter box is checked in the upper left of the screen, select the desired fields and click on the Add button. That will move the selected filter fields to the box in the lower right.

The selected field is added to the Filter Field Name section, and again the delete button will have appeared to allow removal of the selected field if desired, and up and down arrows to move the field up or down the list.

 

Filters will enable you to select the specific information for the report. For example, you may want a report showing companies for Virginia and Maryland. The criteria or "filters" for the report would be that the State Code is equal (=) to VA OR the State Code is equal (=) to MD.

 

The Display Field Names are the "column" headings for your Report.

As such, they (the columns) have a Title.

Display Field Name: This field displays the name of the field chosen. It cannot be modified.

Title: The description of the field. You can change the Title displayed on the report. By default, the title will be the display field name.

Note: The “Display Field Name” field will appear on the extracted report regardless if the “Title” field has been modified. There is no need to change the field name if the information is going to be used in another application rather than simply viewed in the browser.

Size: You can also specify the size (width) of the column for each field. The default width is 1 (inch), but you may wish some columns to be wider, or more narrow than this.

Sort: You can sort the contents of your Display field column by ascending or descending order, or leave it as the default (no sort) order. The drop-down menu choices are: No Order, Ascending, Descending. The field defaults to “No Order”, which means that the field will not appear in order. Ascending or descending can be chosen for both numeric and alphanumeric fields.

Format: You can also format the contents to be lower case or upper case, or leave this option blank. Note: If your Display field column is alphanumeric, but will also show numbers, do NOT format (leave blank), as otherwise the numbers will not be shown.

Formatting is available on date and numeric fields. The Format feature can display the data in a particular fashion. For example, a date field can be displayed with either the long date format (i.e. June 22, 2001) or the short date format (i.e. 6/22/01).

Aggregate: Your last option is Aggregate, and you can select from a drop-down menu of available options (Avg, Count, Min, Max, Sum) or leave it blank. Your choice here is dependent on what your display information actually is.

The aggregate options are to be applied to numeric fields.

Avg – This will show an average of the field.

Count – This will count the number of records for the field.

Min – This will show the minimum value for the field.

Max – This will show the maximum value for the field.

Sum – This will sum the field.

NOTE: These do not provide totals at the bottom of the column. For example, if a report is created using File number and Total Food Revenue per booking and the “SUM” was selected as the aggregate, the report would display the TOTAL of all the bookings revenue for that file in the field. So if the file had five bookings, the report would show the file number once with the total food revenue for all five bookings in the Total Food Revenue Column.

SQL: The SQL statement box is greyed out and therefore unavailable to you, it is for internal Skyware use only.

Click the "Save" button in the Properties box to save any changes you have made and return to the Display pane.

 

The Filter field names are the FILTERS APPLIED TO THE DISPLAY COLUMNS.

For example, if you have added Account # into the Filter field display, you would be able to filter the contents of the display to show only contents that are also associated with that account #.

Note: Remember, you do NOT have to have any fields entered into the Filter field panel to generate a Report. If your expected data is relatively small, you may not need to filter it.

 

Begin and End group: These are both check box options. You do not have to use these, but if you use one you will need to apply the other to a DIFFERENT filter field. Using these options groups Filter fields together.

For example, you could have the Field "Departure Date" entered into the Filter Field TWICE, once with the Begin group box checked, and once with the End group checked. These two entries would then also have different VALUES associated with them here in the Properties box.

 

B: Begin Group: This is to start the filter with an open parenthesis or “(“. You must use parenthesis when choosing multiple queries. The parenthesis sets the order of precedence for the filters (think of it as a mathematical equation where the items in the parenthesis would be calculated first and then applied to the rest of the equation.)

Filter Field Name: This field defaults to the item chosen to have a filter on. You cannot change it.

Operation: The operation determines how the filter is going to be applied to the “value”.

There are two separate operation fields, one refers to the operation you wish to happen, and the second, (referred to as Operator on the Filter Field pane) gives you an AND or OR choice that affects SUBSEQUENT Filter fields.

Choose the operation you wish to happen from the drop-down menu of available options, such as LIKE or NOT LIKE to show entries that are LIKE a particular name, or not (which would show everything different to that name in the report).

As another example, the operator >= with a departure date entry means that your report would display all those entries with a departure date beginning on and later than your specified value, or conversely <= means ending with and before that date value.

There are several choices:

= Equals. The result will be everything equal to the value.

< Less than. The result will be everything less than the value (excluding anything equal to the value).

<= Less than or equal to. The result will be everything less then or equal to the value.

> Greater than. The result will be everything greater than the value (excluding anything equal to the value).

>= Greater than or equal to. The result will be everything greater than or equal to the value.

LIKE. The result will be anything similar to the Filter Field Name. For example, FILE NAME LIKE N, will retrieve all files that start with the letter “N”.

NOT LIKE. The result will exclude anything in the value field. For example, FILE NAME NOT LIKE N, will retrieve all files except ones that start with the letter “N”.

IS NULL. The result will retrieve all records where the value is blank.

Value: This is where the criteria for the “Filter Field Name” are entered. A % sign can be used as a “wild card” when the “LIKE” operation is chosen. The “%” means “contains”. For example, a filter for a room type is created. The result is any room type that contains the letter “D”. The value would be %D%. This would retrieve any records that contained a D regardless of the position of the letter in the room code. For another example, if the result of the report were to get all files that began with the letter “M”, then the value would be M%.

Another example: If you wished to display results between certain dates, these dates would be entered into the Value field for the beginning and end date filter field entries respectively.

Your entry in the Value field is determined from the Filter field you specified.

Operator: This is the second of the two separate Operation fields. There are two choices for the operator - AND and OR.

Using AND will make two filter equal each other.

Using OR will make the filters mutually exclusive of each other.

For example, the report being created is for a specific state code and for a specific sales manager.

The filter would read: Sales Manager = NBD AND State Code = VA. This will retrieve all files for Sales Manager NBD that have a state code of VA. If, for example, the report being created was for two specific market codes, OR would need to be used because a file can only have one market code. (State code is = to VA OR state code is = MD

E: End Group: This is the second Group option. This will close the parenthesis, or add an “)” to the end of the filter. Use with the (B) Begin Group option.

SQL: The SQL statement box is greyed out and therefore unavailable to you, it is for internal Skyware use only.

Click the "Save" button in the Properties box to save any changes you have made and return to the Filter pane.

 

Filter Guidelines:

 

For a filter that show date between two dates, select the date field twice, and user date is greater than AND date is less than

Place any date filters first.

If several filters exist, use the “Begin Group” and “End Group” features to separate or group filters together. Usually, date filters need to be grouped if used with other filters.

 

The order of your Display field names and your Filter field names can be rearranged from the order you have entered them in at any point.

 

For Display field options, the order merely represents the column titles, which appear from left to right on the Report with the first (top) entry being the left-most column, moving in descending order across the report to the right.

Click the up or down arrows to the right of the field name in the Display field pane to move the field position up or down.

 

For Filter field names, your ordering may be more important, especially if you have used the begin/end groups check box options in the Properties area.

Your FIRST entry in the Filter field pane should be YOUR MOST IMPORTANT FILTER.

Subsequent filtering will then be determined by your operation choice of AND or OR.

Choosing AND means that the next filter in descending order will be applied ONLY to the data entries that fulfil the first filter criteria.

Choosing OR means that the next filter in descending order will be applied to those entries that the first filter EXCLUDED.

For example: Your first Filter field is account # and you chose the operation option "like", value Agoda, and operation option "AND".

This means you are filtering your results by those that match ("like") the name (value) Agoda. In other words you have specified the results that match that particular account #. Your choice of AND here means that the next filter you have added will be applied to ONLY those results already matching your first filter. Choosing OR would EXCLUDE the matches from the first filter and thus apply the second filter to everything EXCEPT the first filter matches.

 

Continue adding Fields from the Field Information list to both the Display and Filter fields, and editing their properties, until you are happy with your report contents.

Once you have added all the information fields that you wish to display, and any filters you wish to apply to that data, you should click the save icon in the local tool bar (above the field information panel).

This will open the pop-up window asking for more information. You can change the Query name at this point or simply click OK to return to the Query Builder screen.

Remember, all saved Reports can be accessed from the drop-down list in the "Select Query" option in the local tool bar. When entering the Query Builder screen subsequently, you may choose your Report from here to work on or simply run.

Once your Report has been created, you can choose to either run it (generate it) or send the information in it to a new file (for use with Excel or other options).

Clicking the Run button will generate your Report IN A NEW QUERY SCREEN called Query Results for your viewing.

From here, you can click the Show button to return to the Query Builder screen where you can continue editing the contents of your Report.

 

The filters are critical in obtaining the correct information. If it appears data is missing from the report, all filters should be re-examined. Filters must be placed in a “logical” order. If not, the program will not gather all the needed information. Simply double click on any filter to check it.

If data still appears to be missing, then experiment with changing the orders the filters are listed. Place the more difficult search filters at the top (for example date filters) and easier filters last (for example status filters).

If data still appears to be missing, start with one filter, execute the report and then continue adding filters until the filter causing a problem is identified.

 

After a report has been created, it can be exported to a file and then imported into other applications. To export a report, click on the File button in the tool bar.

Clicking the File button will take you to the File Output screen in the Query Builder, where you have a couple more options to specify before you continue.

Add Header row?: You will need to decide if you wish to add the header row to your data set or not. By default, this check box is selected.

Delimiters: You have two choices here, you will need to choose either the tab or comma radio button for specifying the boundaries between you data entries.

The delimiter determines which cell the field will placed in on a spreadsheet or table. The recommended delimiter is a Tab delimiter. Excel defaults to a tab delimiter when importing the file. Also, there is a less chance of having a tab inside of a field name than a comma. (If, for example, comma delimiter is selected and the File name for a group is ABC Company, Incorporated, the word Incorporated will be placed in a separate cell because the comma determined which cell each item goes in).

By default, tab is selected.

Now click the Save button (below your option choices) to save your Report. This will generate a link to a file for you to download the data.

When the link is generated for the report, right-click on the report and say "Save As". Clicking in the report will open it up in the browser.

 

From here, you can return to the Query Builder screen by clicking on either the Run or File button, then clicking the Show button. This will open the Query Builder screen again, open on your Report.